package app.netant.galgame.analyse.finals;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.lily.com.db.DBFactory;
import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.enviroment.Enviroment;
import cn.lily.com.file.excel.css.ExcelWriterStyle;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.file.excel.spe.IExcelBorder;
import cn.lily.com.file.excel.spe.IExcelColor;
import cn.lily.com.lang.Basic;

/**
 * GalGame最终分析
 * @author JiaJ
 *
 */
public class FinalGalAnalyse {
	
	private Map<String, FinalGalEntity> entitys = new HashMap<String, FinalGalEntity>();
	private List<String> indexs = new ArrayList<String>();
	
	/**
	 * 数据分析
	 */
	public void analyse(){
		
		this.loadAllData();
		
		this.analyseHgamecnData();
		
		this.analyseNyaAyaData();
		
		this.analyseReadyData();
		
		this.writeData();
		
	}
	
	/**
	 * 加载全部数据
	 */
	private void loadAllData(){	
		String sql = "select * from ${schema}.NET_MARGNET_TOL order by MARGNET_ID";
		DataTable dataTable = DBFactory.getThreadDB().executeQueryTable(sql);
		while(dataTable.next()){
			FinalGalEntity entity = new FinalGalEntity();
			entity.setMargnetID(dataTable.getRecord().getStringNotNull("MARGNET_ID"));
			entity.setMargnetUrl(dataTable.getRecord().getStringNotNull("MARGNET_URL"));
			entity.setMargnetFileSize(dataTable.getRecord().getStringNotNull("MARGNET_SIZE"));
			entity.setGalName(dataTable.getRecord().getStringNotNull("MARGNET_NAME"));
			this.entitys.put(dataTable.getString("MARGNET_ID"), entity);
			this.indexs.add(dataTable.getString("MARGNET_ID"));
		}		
	}
	
	private void analyseHgamecnData(){
		String sql = "select * from ${schema}.LEO_HGAMECN_INFO";
		DataTable dataTable = DBFactory.getThreadDB().executeQueryTable(sql);
		
		int hgameIdx = 1;
		
		while(dataTable.next()){
			
			System.out.println("第"+hgameIdx+"开始分析......");
			
			hgameIdx++;
			
			String name = dataTable.getString("GAME_NAME");
			String nameLike = Basic.toWhereLike(name);
			
			System.out.println("HgameCN：【"+name+"】开始分析........");
			
			DataTable table = DBFactory.getThreadDB().executeQueryTable("select * from ${schema}.NET_MARGNET_TOL where MARGNET_NAME like '"+nameLike+"'");
			while(table.next()){
				String margnetID = table.getString("MARGNET_ID");
				FinalGalEntity entity = this.entitys.get(margnetID);
				entity.addHgameCNFetch(name);
			}
			
			String nameOther = dataTable.getString("GAME_NAME_OTH");
			if(nameOther == null || "".equals(nameOther)){
				continue;
			}
			
			for(String activeName:nameOther.split("/")){
				
				String nameOtherLike = Basic.toWhereLike(activeName);				
				System.out.println("HgameCN：【"+activeName+"】开始分析........");
						
				DataTable activeNameTable = DBFactory.getThreadDB().executeQueryTable("select * from ${schema}.NET_MARGNET_TOL where MARGNET_NAME like '"+nameOtherLike+"'");
				while(activeNameTable.next()){
					String margnetID = activeNameTable.getString("MARGNET_ID");
					FinalGalEntity entity = this.entitys.get(margnetID);
					entity.addHgameCNFetch(activeName);
				}
				
			}
			
		}

	}
	
	private void analyseNyaAyaData(){
		
		String sql = "select * from ${schema}.LEO_YUINYA_GAL_INFO";
		DataTable dataTable = DBFactory.getThreadDB().executeQueryTable(sql);
		
		int yunAyaIdx = 1;
		
		while(dataTable.next()){
			
			System.out.println("第"+yunAyaIdx+"开始分析......");
			
			yunAyaIdx++;
			
			String name = dataTable.getString("GAME_NAME");
			String nameLike = Basic.toWhereLike(name);
			
			System.out.println("YunAya：【"+name+"】开始分析........");
			
			DataTable table = DBFactory.getThreadDB().executeQueryTable("select * from ${schema}.NET_MARGNET_TOL where MARGNET_NAME like '"+nameLike+"'");
			while(table.next()){
				String margnetID = table.getString("MARGNET_ID");
				FinalGalEntity entity = this.entitys.get(margnetID);
				entity.addYuinyaFetch(name);
			}
			
			
		}

		
	}
	
	public void analyseReadyData(){
		
		String sql = "select * from ${schema}.LEO_GALGAME_INFO_JPS ";
		DataTable dataTable = DBFactory.getThreadDB().executeQueryTable(sql);
		
		int readyIdx = 1;
		
		while(dataTable.next()){
			
			System.out.println("第"+readyIdx+"开始分析......");
			
			readyIdx++;
			
			String name = dataTable.getString("GAME_NAME");
			String nameLike = Basic.toWhereLike(name);
			
			System.out.println("Ready：【"+name+"】开始分析........");
			
			DataTable table = DBFactory.getThreadDB().executeQueryTable("select * from ${schema}.NET_MARGNET_TOL where MARGNET_NAME like '"+nameLike+"'");
			while(table.next()){
				String margnetID = table.getString("MARGNET_ID");
				FinalGalEntity entity = this.entitys.get(margnetID);
				entity.addReadyFetch(name);
			}
			
			String nameOther = dataTable.getString("GAME_NAME_PLUS");
			if(nameOther == null || "".equals(nameOther)){
				continue;
			}
			
			for(String activeName:nameOther.split(",")){
				
				String nameOtherLike = Basic.toWhereLike(activeName);				
				System.out.println("Ready：【"+activeName+"】开始分析........");
						
				DataTable activeNameTable = DBFactory.getThreadDB().executeQueryTable("select * from ${schema}.NET_MARGNET_TOL where MARGNET_NAME like '"+nameOtherLike+"'");
				while(activeNameTable.next()){
					String margnetID = activeNameTable.getString("MARGNET_ID");
					FinalGalEntity entity = this.entitys.get(margnetID);
					entity.addReadyFetch(activeName);
				}
				
			}
			
		}
		
	}
	
	/**
	 * 输出数据
	 */
	private void writeData(){
		
		ExcelWriter ew = new ExcelWriter("E:/finAny.xlsx");	
		ew.inst(100);
				
		int sheetIdx = 0;
		int rowIdx = 0;
		
		ExcelWriterStyle titleStyle = new ExcelWriterStyle(ew);
		titleStyle.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
							.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
							.setBorder("left", IExcelBorder.BORDER_DOUBLE)
							.setBorder("right", IExcelBorder.BORDER_DOUBLE)
							.setBackGroundColor(IExcelColor.BLUE_GREY);
		
		ExcelWriterStyle greenStyle = new ExcelWriterStyle(ew);
		greenStyle.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
							.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
							.setBorder("left", IExcelBorder.BORDER_DOUBLE)
							.setBorder("right", IExcelBorder.BORDER_DOUBLE)
							.setBackGroundColor(IExcelColor.LIGHT_GREEN);
		
		ExcelWriterStyle orangeStyle = new ExcelWriterStyle(ew);
		orangeStyle.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
							.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
							.setBorder("left", IExcelBorder.BORDER_DOUBLE)
							.setBorder("right", IExcelBorder.BORDER_DOUBLE)
							.setBackGroundColor(IExcelColor.LIGHT_ORANGE);
		
		ExcelWriterStyle yellowStyle = new ExcelWriterStyle(ew);
		yellowStyle.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
							.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
							.setBorder("left", IExcelBorder.BORDER_DOUBLE)
							.setBorder("right", IExcelBorder.BORDER_DOUBLE)
							.setBackGroundColor(IExcelColor.LIGHT_YELLOW);
		
		ExcelWriterStyle greyStyle = new ExcelWriterStyle(ew);
		greyStyle.getCell().setBorder("top", IExcelBorder.BORDER_DOUBLE)
							.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
							.setBorder("left", IExcelBorder.BORDER_DOUBLE)
							.setBorder("right", IExcelBorder.BORDER_DOUBLE);
		
		for(String key:this.indexs){
			
			ew.locateSheet(sheetIdx);			
			ew.locateRow(rowIdx);
			
			if(rowIdx == 0){

				ew.fressFirstCol();
				
				ew.setCellValue(0,"游戏名");
				ew.setCellValue(1,"磁力链接地址");
				ew.setCellValue(2,"文件体积");
				ew.setCellValue(3,"HGAMECN");
				ew.setCellValue(4,"YunAya");
				ew.setCellValue(5,"Ready");
				
				ew.setStyle(0,titleStyle);
				ew.setStyle(1,titleStyle);
				ew.setStyle(2,titleStyle);
				ew.setStyle(3,titleStyle);
				ew.setStyle(4,titleStyle);
				ew.setStyle(5,titleStyle);
						
				rowIdx++;
				ew.locateRow(rowIdx);
			}
			
			FinalGalEntity entity = entitys.get(key);
			ew.setCellValue(0,entity.getGalName());
			ew.setCellValue(1,entity.getMargnetUrl());
			ew.setCellValue(2,entity.getMargnetFileSize());	
			ew.setCellValue(3,entity.getHgameCNFetch());
			ew.setCellValue(4,entity.getYuinyaFetch());
			ew.setCellValue(5,entity.getReadyFetch());
			ew.setColWith(0, 700);
			ew.setColWith(1, 490);
			ew.setColWith(2, 80);
			ew.setColWith(3, 400);
			ew.setColWith(4, 400);
			ew.setColWith(5, 400);
			
			for(int i=0;i<=5;i++){
				if(entity.getReadyFetch()!=null && !"".equals(entity.getReadyFetch())){
					ew.setStyle(i,greenStyle);
				}
				else if(entity.getYuinyaFetch()!=null && !"".equals(entity.getYuinyaFetch())){
					ew.setStyle(i,orangeStyle);
				}
				else if(entity.getHgameCNFetch()!=null && !"".equals(entity.getHgameCNFetch())){
					ew.setStyle(i,yellowStyle);
				}
				else{
					ew.setStyle(i,greyStyle);
				}
			}
				
			if(rowIdx == 300){			
				rowIdx = 0;
				sheetIdx++;
			}else{
				rowIdx++;
			}		
		}
		
		ew.freezeCol(0);
		
		ew.write();
		ew.free();
		
	}
	
	/**
	 * 数据清理
	 */
	public void clearData(){
		
		String sql = "select * from ( "
				   + "select count(*) as TOLCONT, MARGNET_URL from ( "
				   + "select MARGNET_ID,UPPER(MARGNET_URL) as MARGNET_URL from ${schema}.NET_MARGNET_TOL ) "
				   + "group by MARGNET_URL ) "
				   + "where TOLCONT>1 ";
		DataTable clearTable = DBFactory.getThreadDB().executeQueryTable(sql);
		while(clearTable.next()){
			String marUrl = clearTable.getString("MARGNET_URL");
			DataTable tmpTable = DBFactory.getThreadDB().executeQueryTable("select * from ${schema}.NET_MARGNET_TOL where UPPER(MARGNET_URL)='"+marUrl+"'");
			if(tmpTable.getRecordCount()==2){
				tmpTable.next();
				String marID = tmpTable.getString("MARGNET_ID");
				DBFactory.getThreadDB().executeUpdate("delete from ${schema}.NET_MARGNET_TOL where MARGNET_ID='"+marID+"'");
			}
		}
		
	}
	
	public static void main(String[] args) {
		
		Enviroment.createEnv();
		Enviroment.getEnv().getLogBean().beginLog();
		
		FinalGalAnalyse fin = new FinalGalAnalyse();
		fin.analyse();
		//fin.clearData();
	}
	
}
